﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace FriendlyFire
{
    public class DBHelper
    {
        //TODO implement a retry mechanism to handle concurrency errors.
        SQLiteConnection conn;
        string db_location = "storage.db";
        // fuck it. no internet to figureout settings.settings access issue. piece of shit.
        // We'll do it live!!!
        string[] extArch = { ".zip", ".7z", ".rar", ".tar", ".gz", ".bz2", ".tgz" };
        string[] extAud = { ".mp3", ".wma", ".wav", ".ogg", ".aac", ".flac" };
        string[] extDocs = { ".doc", ".txt", ".rtf", ".docx", ".xls", ".odt" };
        string[] extVid = { ".divx", ".avi", ".wmv", ".mov", ".flv", ".mpg", ".mkv" };

        #region Accessors
        public string Db_Location
        {
            get { return db_location; }
            set { db_location = value; }
        }
        public string[] ExtVid
        {
            get { return extVid; }
        }
        public string[] ExtDocs
        {
            get { return extDocs; }
        }
        public string[] ExtAud
        {
            get { return extAud; }
        }
        public string[] ExtArch
        {
            get { return extArch; }
        }
        #endregion
        #region Constructor
        public DBHelper()
        {
            conn = new SQLiteConnection("data source=storage.db;cache size=8000");
            conn.Open(); // just keep the connection open. for now. doesn't seem to be an issue.
        }
        #endregion
        #region GetConfig(string key) -> string
        public string GetConfig(string key)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT value FROM config WHERE key = @mykey";
            SQLiteParameter p = cmd.CreateParameter();
            p.Value = key;
            p.IsNullable = false;
            p.Direction = ParameterDirection.Input;
            p.ParameterName = "@mykey";
            cmd.Parameters.Add(p);
            //conn.Open();
            object temp = cmd.ExecuteScalar();
            //conn.Close();
            return (string)temp;
        }
        #endregion
        #region InsertNewConfig(string key, string value) -> int
        public int InsertNewConfig(string key, string value)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO config ('key', 'value') VALUES (@key, @value);";
            cmd.Parameters.Add("@key", DbType.String).Value = key;
            cmd.Parameters.Add("@value", DbType.String).Value = value;
            //conn.Open();
            return cmd.ExecuteNonQuery();
        }
        #endregion
        #region SetConfig(string key, string value) -> string
        public int SetConfig(string key, string value)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "UPDATE config SET value=@myval WHERE key = @mykey";
            cmd.Parameters.Add("@myval", System.Data.DbType.String).Value = value;
            cmd.Parameters.Add("@mykey", System.Data.DbType.String).Value = key;
            //conn.Open();
            int status = cmd.ExecuteNonQuery();
            //conn.Close();
            return status;
        }
        #endregion
        #region GetFriends() -> Dictionary<int, string>
        public Dictionary<int, string> GetFriends()
        {
            Dictionary<int, string> friends = new Dictionary<int, string>();
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT id, handle FROM friend ORDER BY handle";
            //conn.Open();
            SQLiteDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                friends.Add((int)dr[0], (string)dr[1]);
            }
            //conn.Close();
            return friends;
        }
        #endregion
        #region GetIndexDirs() -> Dictionary<int, string>
        public Dictionary<long, string> GetIndexDirs()
        {
            Dictionary<long, string> dirs  = new Dictionary<long, string>();
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT id, path FROM lib_index ORDER BY path";
            //conn.Open();
            SQLiteDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                dirs.Add(Convert.ToInt64(dr[0]), dr[1].ToString());
            }
            //conn.Close();
            return dirs;
        }
        #endregion
        #region PerformSearchOnLocalIndex(SearchFor info) -> DataSet
        public DataSet PerformSearchOnLocalIndex(SearchFor info)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            /* DataSet structure
                col1 = id
                col2 = name
                col3 = extension (.xyz)
                col4 = path (C:\yarr)
                col5 = metadataid
             */
            DataSet ds;
            SQLiteDataAdapter da;
            
            if (info.LookAt.Contains(SearchForTypes.FileType.Anything) || info.LookAt.Count.Equals(0)) // overrides all others
            {
                cmd.CommandText = "SELECT id, name, extension, path, metadataid FROM file WHERE name LIKE '%" + info.Query+"%'";
                //cmd.Parameters.Add("@query", DbType.String).Value = info.Query;
                //conn.Open();
                ds = new DataSet("results");
                da = new SQLiteDataAdapter(cmd);
                da.Fill(ds);
                //conn.Close();
                cmd = null;
                da = null;
                return ds;
            }
            #region query logic
            bool hasAND = false;
            bool zeroStart = true;
            string sql = "SELECT id, name, extension, path,  metadataid FROM file WHERE name LIKE '%" + info.Query + "%'";
            if (info.LookAt.Contains(SearchForTypes.FileType.Archives))
            {
                sql += " AND extension = '" + extArch[0] + "'";
                hasAND = true;
                for (int i = 1; i < extArch.Length; i++)
                {
                    sql += " OR extension = '" + extArch[i] + "'";
                    sql += " AND name LIKE '%" + info.Query + "%'";
                }
            }
            if (info.LookAt.Contains(SearchForTypes.FileType.Audio))
            {
                zeroStart = true;
                if (!hasAND)
                {
                    sql += " AND extension = '" + extAud[0] + "'";
                    hasAND = true;
                    zeroStart = false;
                }
                for (int i = ((zeroStart) ? 0 : 1); i < extAud.Length; i++)
                {
                    sql += " OR extension = '" + extAud[i] + "'";
                    sql += " AND name LIKE '%" + info.Query + "%'";
                }
            }
            if (info.LookAt.Contains(SearchForTypes.FileType.Documents))
            {
                zeroStart = true;
                if (!hasAND)
                {
                    sql += " AND extension = '" + extDocs[0] + "'";
                    hasAND = true;
                    zeroStart = false;
                }
                for (int i = ((zeroStart) ? 0 : 1); i < extDocs.Length; i++)
                {
                    sql += " OR extension = '" + extDocs[i] + "'";
                    sql += " AND name LIKE '%" + info.Query + "%'";
                }
            }
            if (info.LookAt.Contains(SearchForTypes.FileType.Video))
            {
                zeroStart = true;
                if (!hasAND)
                {
                    sql += " AND extension = '" + extVid[0] + "'";
                    hasAND = true;
                    zeroStart = false;
                }
                for (int i = ((zeroStart) ? 0 : 1); i < extVid.Length; i++)
                {
                    sql += " OR extension = '" + extVid[i] + "'";
                    sql += " AND name LIKE '%" + info.Query + "%'";
                }
            }
            if (info.LookAt.Contains(SearchForTypes.FileType.Custom))
            {
                if (!hasAND)
                {
                    sql += " AND extension = '" + info.CustomType + "'";
                    hasAND = true;
                }
                else
                {
                    sql += " OR extension = '" + info.CustomType + "'";
                    sql += " AND name LIKE '%" + info.Query + "%'";
                }
            }
            #endregion
            cmd.CommandText = sql;
            string s = cmd.CommandText;
            //conn.Open();
            ds = new DataSet("results");
            da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            //conn.Close();
            cmd = null;
            da = null;
            return ds;
        }
        #endregion
        #region GetLastInsertID() -> long
        /// <summary>
        /// calls the sqlite internal function to get the 
        /// id of the last inserted record.
        /// </summary>
        /// <returns>the last auto increment id if successful, -1 if it fails.</returns>
        public long GetLastInsertID()
        {
            long last = -1;
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT Last_Insert_Rowid()";
            try
            {
                //conn.Open();
                last = Convert.ToInt64(cmd.ExecuteScalar());
                //conn.Close();
                cmd = null;
            }
            catch { }
            return last;
        }
        #endregion
        #region CheckDirectoryCount(string path) -> int
        /// <summary>
        /// Checks to see if a directory is already in the list
        /// of indexed paths.
        /// </summary>
        /// <param name="path">the path we're looking for</param>
        /// <returns>0 if not found, 1 if it is found.</returns>
        public long CheckDirectoryCount(string path)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT id FROM lib_index WHERE path = @path";
            cmd.Parameters.Add("@path", DbType.String).Value = path;
            //conn.Open();
            object result = cmd.ExecuteScalar();
            //conn.Close();
            cmd = null;
            return ((result == null) ? 0 : 1);
        }
        #endregion
        #region InsertNewIndexedDirectory(string path) -> int
        /// <summary>
        /// Insert a new path that needs to be indexed.
        /// </summary>
        /// <param name="path">the path to insert.</param>
        /// <returns>1 if the insert was successfull.</returns>
        public int InsertNewIndexedDirectory(string path)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO lib_index ('path') VALUES (@path);";
            cmd.Parameters.Add("@path", DbType.String).Value = path;
            //conn.Open();
            int result = cmd.ExecuteNonQuery();
            //conn.Close();
            return result;
        }
        #endregion
        #region DeleteIndexedDirectory(string path) -> int
        public int DeleteIndexedDirectory(KeyValuePair<long,string> path)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "DELETE FROM lib_index WHERE path = @path";
            cmd.Parameters.Add("@path", DbType.String).Value = path.Value;
            //conn.Open();
            int result = cmd.ExecuteNonQuery();
            //conn.Close();
            cmd = null;
            return result;
        }
        #endregion
        #region ResetIndex() -> bool
        public bool ResetIndex()
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "DELETE FROM playlist";
            try
            {
                //conn.Open();
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM metadata";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM listdetail";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM lib_index";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM file";
                cmd.ExecuteNonQuery();
                //conn.Close();
                cmd = null;
                return true;
            }
            catch
            {
                return false;
            }

        }
        #endregion
        #region ResetLists() -> bool
        public bool ResetLists()
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "DELETE FROM playlist";
            try
            {
                //conn.Open();
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM listdetail";
                cmd.ExecuteNonQuery();
                //conn.Close();
                cmd = null;
                return true;
            }
            catch
            {
                return false;
            }

        }
        #endregion
        #region CountFilesIndexed() -> long
        public long CountFilesIndexed()
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT COUNT(id) FROM file";
            //conn.Open();
            object result = cmd.ExecuteScalar();
            //conn.Close();
            return Convert.ToInt64(result);
        }
        #endregion
        #region CountFilesWithGivenExtension(string ext) -> long
        public long CountFilesWithGivenExtension(string ext)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT COUNT(id) FROM file WHERE extension = @ext";
            cmd.Parameters.Add("@ext", DbType.String).Value = ext;
            object result = cmd.ExecuteScalar();
            return Convert.ToInt64(result);
        }
        #endregion

        #region InsertNewFileRecord
        public void InsertNewFileRecord(FileDataInfo f)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO file ('name', 'extension', 'path', 'metadataid') VALUES (@name, @extension, @path, @metadataid);";
            cmd.Parameters.Add("@name", DbType.String).Value = f.Name;
            cmd.Parameters.Add("@extension", DbType.String).Value = f.Extension;
            cmd.Parameters.Add("@path", DbType.String).Value = f.Path;
            cmd.Parameters.Add("@metadataid", DbType.UInt32).Value = f.MetadataID;
            int result = cmd.ExecuteNonQuery();
        }
        #endregion 
        #region InsertNewMetaDataRecord
        public void InsertNewMetaDataRecord(MetaDataInfo m)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO metadata ('album', 'artist', 'composer', 'genre', 'title', 'year', " +
                            "'track', 'duration', 'bitrate', 'bytesize', 'height', 'width') " +
                            "VALUES (@album, @artist, @composer, @genre, @title, @year, " +
                            "@track, @duration, @bitrate, @bytesize, @height, @width);";
            cmd.Parameters.Add("@album", DbType.String).Value = m.Album ?? "";
            cmd.Parameters.Add("@artist", DbType.String).Value = m.Artist ?? "";
            cmd.Parameters.Add("@composer", DbType.String).Value = m.Composer ?? "";
            cmd.Parameters.Add("@genre", DbType.String).Value = m.Genre ?? "";
            cmd.Parameters.Add("@title", DbType.String).Value = m.Title ?? "";
            if (m.Year.Equals(null)) {
                cmd.Parameters.Add("@year", DbType.UInt32).Value = 0; }
            else {
                cmd.Parameters.Add("@year", DbType.UInt32).Value = m.Year; }
            if (m.Track.Equals(null)) {
                cmd.Parameters.Add("@track", DbType.UInt32).Value = 0; }
            else {
                cmd.Parameters.Add("@track", DbType.UInt32).Value = m.Track; }
            cmd.Parameters.Add("@duration", DbType.String).Value = m.Duration ?? "";
            if (m.Bitrate.Equals(null)) {
                cmd.Parameters.Add("@bitrate", DbType.Int32).Value = 0; }
            else {
                cmd.Parameters.Add("@bitrate", DbType.Int32).Value = m.Bitrate; }
            if (m.ByteSize.Equals(null)) {
                cmd.Parameters.Add("@bytesize", DbType.UInt64).Value = 0; }
            else {
                cmd.Parameters.Add("@bytesize", DbType.UInt64).Value = m.ByteSize; }
            if (m.Height.Equals(null)) {
                cmd.Parameters.Add("@height", DbType.Int32).Value = 0; }
            else {
                cmd.Parameters.Add("@height", DbType.Int32).Value = m.Height; }
            if (m.Width.Equals(null)) {
                cmd.Parameters.Add("@width", DbType.Int32).Value = 0; }
            else
                cmd.Parameters.Add("@width", DbType.Int32).Value = m.Width;

            int result = cmd.ExecuteNonQuery();
        }
        #endregion 
        #region Cleanup "vacuum" Database
        public void CleanupDB()
        {
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "VACUUM";
                cmd.ExecuteNonQuery();
            }
        }
        #endregion
        #region DeletePlayList(long listID) -> int
        public int DeletePlayList(long listID)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "DELETE FROM playlist WHERE id = @id";
            cmd.Parameters.Add("@id", DbType.Int64).Value = listID;
            int result = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd.CommandText = "DELETE FROM listdetail WHERE listid = @listid";
            cmd.Parameters.Add("@listid", DbType.Int64).Value = listID;
            result += cmd.ExecuteNonQuery();
            cmd = null;
            return result;
        }
        #endregion
        #region GetPlayLists() -> Dictionary<int, string>
        public Dictionary<long, string> GetPlayLists()
        {
            Dictionary<long, string> playlists = new Dictionary<long, string>();
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT id, name FROM playlist ORDER BY name ASC";
            SQLiteDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                playlists.Add(Convert.ToInt64(dr[0]), dr[1].ToString());
            }
            return playlists;
        }
        #endregion
        #region GetPlayListAsQueueItemList(long listID) -> List<QueueItem>
        public List<QueueItem> GetPlayListAsQueueItemList(long listID)
        {
            List<QueueItem> qi = new List<QueueItem>();
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT fileid FROM listdetail WHERE listid = @listid";
            cmd.Parameters.Add("@listid", DbType.Int64).Value = listID;
            SQLiteDataReader dr = cmd.ExecuteReader();
            long fileid;
            while (dr.Read())
            {
                fileid = (long)dr[0];
                qi.Add(new QueueItem(GetDirectoryForFile(fileid),
                        GetNameForFile(fileid),fileid));
            }
            return qi;
        }
        #endregion
        #region string GetDirectoryForFile(long fileID) -> string
        public string GetDirectoryForFile(long fileID)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT path FROM file WHERE id = @id";
            cmd.Parameters.Add("@id", DbType.Int64).Value = fileID;
            return cmd.ExecuteScalar().ToString();
        }
        #endregion
        #region string GetNameForFile(long fileID) -> string
        public string GetNameForFile(long fileID)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT name FROM file WHERE id = @id";
            cmd.Parameters.Add("@id", DbType.Int64).Value = fileID;
            return cmd.ExecuteScalar().ToString();
        }
        #endregion
        #region SavePlayList() -> int
        public void SavePlayList(string lname, System.Windows.Forms.ListBox.ObjectCollection items)
        {
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO playlist ('name') VALUES (@name)";
            cmd.Parameters.Add("@name", DbType.String).Value = lname;
            int result = cmd.ExecuteNonQuery();
            
            long playlistID = GetLastInsertID();

            cmd.CommandText = "INSERT INTO listdetail ('listid', 'fileid') VALUES (@listid, @fileid)";
            foreach (object o in items)
            {
                cmd.Parameters.Clear();
                QueueItem q = o as QueueItem;
                if (q != null)
                {
                    cmd.Parameters.Add("@listid", DbType.Int64).Value = playlistID;
                    cmd.Parameters.Add("@fileid", DbType.Int64).Value = q.Fileid;
                    cmd.ExecuteNonQuery();
                }
            }
            
        }
        #endregion
    }
}
